package backcode;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;
import java.util.Vector;

public class DatabaseSystemLink {
    private final static String url = "jdbc:mysql:///database_project_formal?useSSL=false&useServerPrepStmts=true";
    private final static String username = "root";
    private final static String password = "h2057232978";


    static Scanner input = new Scanner(System.in);

    //数据库连接
    public static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection(url, username, password);
    }

    private static void close(ResultSet rs, PreparedStatement pst) throws SQLException {
        rs.close();
        pst.close();
    }

    public static void close(ResultSet rs, PreparedStatement pst, Connection conn) throws SQLException {
        rs.close();
        pst.close();
        conn.close();
    }

    public static void close(PreparedStatement pst, Connection conn) throws SQLException {
        pst.close();
        conn.close();
    }
    //编号

    /**
     * 获得type类型的前几位的号码
     *
     * @param type 类型
     * @return 前几位号码
     * @throws Exception 数据库连接异常
     */
    public static String getFirstNumber(String type) throws Exception {
        String res = null;
        Connection conn = getConnection();
        String sql = "select * from first_id where type=? ";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, type);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            res = rs.getString("number");
        }
        close(rs, pst, conn);
        return res;
    }

    /**
     * 获得type类型的最后几位的号码
     *
     * @param type 类型
     * @return 最后的几位号码
     * @throws Exception 数据库连接异常
     */
    public static int getLastNumber(String type) throws Exception {
        int res = 0;
        Connection conn = getConnection();
        String sql = "select * from last_id where type=? ";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, type);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            res = rs.getInt("number");
        }
        close(rs, pst, conn);

        return res;
    }

    /**
     * 更新编号
     *
     * @param type 修改编号的类型
     * @return 成功与否
     * @throws Exception 数据库连接异常
     */
    public static boolean updateLastNumber(String type) throws Exception {
        Connection conn = getConnection();
        String sql = "update last_id set number=number+1 where type=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, type);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 查询编号是否存在
     *
     * @param id   编号
     * @param type 工种
     *             业务员 backcode.Salesman
     *             会计 backcode.Accountant
     *             顾客 backcode.Consumer
     * @return 存在与否
     * @throws Exception 数据库连接异常
     */
    public static boolean isExistedIdNumber(String id, String type) throws Exception {
        Connection conn = getConnection();
        String sql;
        switch (type) {
            case Accountant.IDENTITY -> sql = "select * from accountant where ac_id_number=?";
            case Salesman.IDENTITY -> sql = "select * from salesman where sa_id_number=?";
            case Consumer.IDENTITY -> sql = "select * from consumer where con_id_number=?";
            case Goods.IDENTITY -> sql = "select * from goods where goods_id_number=?";
            case Order.IDENTITY -> sql = "select * from _order where order_id_number=?";
            default -> throw new IllegalStateException("Unexpected value: " + type);
        }

        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        ResultSet rs = pst.executeQuery();
        boolean b = rs.next();
        close(rs, pst, conn);
        return b;
    }

    /**
     * 根据顾客编号获得其订单数量
     *
     * @param id 顾客的编号
     * @return 顾客订单的数量
     * @throws Exception 数据库连接数量
     */
    public static int getOrderAmount(String id) throws Exception {
        int res = 0;
        Connection conn = getConnection();

        String sql = "select * from consumer where con_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            res = rs.getInt("order_amount");
        }
        close(rs, pst, conn);
        return res;
    }

    /**
     * 更新订单的数量
     *
     * @param id 顾客的编号
     * @return 更新成功与否
     * @throws Exception 数据库连接异常
     */
    public static boolean updateOrderAmount(String id) throws Exception {
        Connection conn = getConnection();
        String sql = "update consumer set order_amount=order_amount+1 where con_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 查询订单编号是否存在
     *
     * @param id 订单编号
     * @return 存在与否
     * @throws Exception 数据库连接异常
     */
    public static boolean isExistedOrder(String id) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from _order where order_id_number=? ";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        ResultSet rs = pst.executeQuery();
        boolean b = rs.next();
        close(rs, pst, conn);
        return b;
    }

    //员工

    /**
     * 存储员工信息
     *
     * @param staff 员工对象
     * @param type  工种
     * @return 存储成功与否
     * @throws Exception
     */
    public static boolean saveStaffData(Staff staff, String type) throws Exception {
        Connection conn = getConnection();
        String sql = null;
        switch (type) {
            case Accountant.IDENTITY ->
                    sql = "insert into accountant(ac_id_number,ac_name,ac_phone_number,ac_username,ac_password) values(?,?,?,?,?)";
            case Salesman.IDENTITY ->
                    sql = "insert into salesman(sa_id_number,sa_name,sa_phone_number,sa_username,sa_password) values(?,?,?,?,?)";
        }
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, staff.getIdNumber());
        pst.setString(2, staff.getName());
        pst.setString(3, staff.getPhoneNumber());
        pst.setString(4, staff.getUsername());
        pst.setString(5, staff.getPassword());
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 查询员工用户名是否存在
     *
     * @param username 用户名
     * @param identity 身份/工种
     * @return 是否存在
     * @throws Exception 数据库连接异常
     */
    public static boolean isExistedUsername(String username, String identity) throws Exception {
        boolean b;
        Connection conn = getConnection();
        String sql = null;

        switch (identity) {
            case Accountant.IDENTITY:
                sql = "select * from accountant where ac_username=?";
                break;
            case Salesman.IDENTITY:
                sql = "select * from salesman where sa_username=?";
                break;
        }
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, username);
        ResultSet rs = pst.executeQuery();
        if (rs.next()) {
            b = true;
        } else {
            b = false;
        }
        rs.close();
        pst.close();
        conn.close();
        return b;
    }

    /**
     * 通过用户名和密码对数据库进行查询，如果能查到则登录成功。
     *
     * @param username
     * @param password
     * @return
     * @throws Exception
     */
    public static boolean staffLogin(String username, String password, String type) throws Exception {
        boolean b;
        //建立连接
        Connection conn = getConnection();
        //定义sql语句
        String sql = null;
        switch (type) {
            case Accountant.IDENTITY -> sql = "select * from accountant where ac_username=? and ac_password=?";
            case Salesman.IDENTITY -> sql = "select * from salesman where sa_username=? and sa_password=?";
        }

        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, username);
        pst.setString(2, password);
        ResultSet rs = pst.executeQuery();
        b = rs.next();
        close(rs, pst, conn);
        return b;
    }
    //商品

    /**
     * 存储商品信息
     *
     * @param goods
     * @return
     * @throws Exception
     */
    public static boolean saveGoodsData(Goods goods) throws Exception {
        Connection conn = getConnection();
        String sql = "insert into goods(goods_id_number,goods_name,goods_price,goods_amount) values(?,?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, goods.getGoodsIdNumber());
        pst.setString(2, goods.getGoodsName());
        pst.setDouble(3, goods.getGoodsPrice());
        pst.setInt(4, goods.getGoodsAmount());
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 进货
     *
     * @param goodsIdNumber
     * @param goodsAmount
     * @return
     * @throws Exception
     */
    public static boolean replenishment(String goodsIdNumber, int goodsAmount) throws Exception {
        Connection conn = getConnection();
        String sql = "update goods set goods_amount=goods_amount+? where goods_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1, goodsAmount);
        pst.setString(2, goodsIdNumber);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 判断商品是否足够。
     *
     * @param goodsIdNumber
     * @param goodsAmount
     * @return
     * @throws Exception
     */
    public static boolean isEnoughGoods(String goodsIdNumber, int goodsAmount) throws Exception {
        boolean b = true;
        Connection conn = getConnection();
        String sql = "select goods_amount from goods where goods_id_number=?  ";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, goodsIdNumber);
        ResultSet rs = pst.executeQuery();
        while (rs.next() == true) {
            int amount = rs.getInt("goods_amount");
            if (amount < goodsAmount) {
                b = false;
            }
        }
        close(rs, pst, conn);
        return b;
    }

    /**
     * 卖货
     *
     * @param goodsIdNumber
     * @param goodsAmount
     * @return
     * @throws Exception
     */
    public static boolean reduceGoods(String goodsIdNumber, int goodsAmount) throws Exception {
        Connection conn = getConnection();
        String sql = "update goods set goods_amount=goods_amount-? where goods_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setInt(1, goodsAmount);
        pst.setString(2, goodsIdNumber);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 将goods表中数据读到data中
     *
     * @param data 存储表中数据
     * @throws Exception 数据库连接异常
     */
    public static void displayData(Vector<Vector<Object>> data) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from goods";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            Vector<Object> rowData = new Vector<>();
            String idNumber = rs.getString("goods_id_number");
            String name = rs.getString("goods_name");
            double price = rs.getDouble("goods_price");
            int amount = rs.getInt("goods_amount");
            rowData.addElement(idNumber);
            rowData.addElement(name);
            rowData.addElement(price);
            rowData.addElement(amount);
            data.addElement(rowData);
        }
        close(rs, pst, conn);
    }

    /**
     * 将goods表中商品编号为id的读到data中
     *
     * @param data 存储数据
     * @param id   商品编号
     * @throws Exception 数据库连接异常
     */
    public static void displayData(Vector<Vector<Object>> data, String id) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from goods where goods_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            Vector<Object> rowData = new Vector<>();
            String idNumber = rs.getString("goods_id_number");
            String name = rs.getString("goods_name");
            double price = rs.getDouble("goods_price");
            int amount = rs.getInt("goods_amount");
            rowData.addElement(idNumber);
            rowData.addElement(name);
            rowData.addElement(price);
            rowData.addElement(amount);
            data.addElement(rowData);
        }
        close(rs, pst, conn);
    }

    //更新库中商品
    public static boolean updateGoods(String id, Goods goods) throws Exception {
        Connection conn = getConnection();
        String sql = "update goods set goods_name=? ,goods_amount=? ,goods_price=? where goods_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, goods.getGoodsName());
        pst.setInt(2, goods.getGoodsAmount());
        pst.setDouble(3, goods.getGoodsPrice());
        pst.setString(4, id);
        int i = pst.executeUpdate();
        return i == 1;
    }
    //顾客

    /**
     * 根据顾客的姓名判断顾客是否存在
     *
     * @param name 顾客的姓名
     * @return 顾客存在与否
     * @throws Exception 数据库连接异常
     */
    public static boolean isExistedConsumer(String name) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from consumer where con_name=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, name);
        ResultSet rs = pst.executeQuery();
        boolean b = rs.next();
        close(rs, pst, conn);
        return b;
    }

    /**
     * 存储顾客的基本信息
     *
     * @param consumer 顾客对象
     * @return 存储成功与否
     * @throws Exception 数据库连接异常
     */
    public static boolean saveConsumerBasicData(Consumer consumer) throws Exception {
        Connection conn = getConnection();
        String sql = "insert into consumer(con_id_number,con_name,con_phone_number,order_amount) values(?,?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, consumer.getConIdNumber());
        pst.setString(2, consumer.getConName());
        pst.setString(3, consumer.getConPhoneNumber());
        pst.setInt(4, 1);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    /**
     * 根据顾客姓名获得库中顾客的编号
     *
     * @param name 顾客姓名
     * @return 顾客编号
     * @throws Exception 数据库连接异常
     */
    public static String getIdNumberConsumer(String name) throws Exception {
        Connection conn = getConnection();
        String id = null;
        String sql = "select * from consumer where con_name=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, name);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            id = rs.getString("con_id_number");
        }
        close(rs, pst, conn);
        return id;
    }

//获取顾客数据

    /**
     * 将客户表中姓名为name的数据读进data中
     *
     * @param data 存储数据
     * @param name 客户姓名
     * @throws Exception 数据库连接异常
     */
    public static void displayConData(Vector<Vector<Object>> data, String name) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from consumer where con_name=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, name);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            Vector<Object> rowData = new Vector<>();
            String idNumber = rs.getString("con_id_number");
            String con_name = rs.getString("con_name");
            String phoneNumber = rs.getString("con_phone_number");
            int amount = rs.getInt("order_amount");
            rowData.addElement(idNumber);
            rowData.addElement(con_name);
            rowData.addElement(phoneNumber);
            rowData.addElement(amount);
            data.addElement(rowData);
        }
        close(rs, pst, conn);
    }

    /**
     * 将客户表中数据全部读进data中
     *
     * @param data 存储数据
     * @throws Exception 数据库连接异常
     */
    public static void displayConData(Vector<Vector<Object>> data) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from consumer";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            Vector<Object> rowData = new Vector<>();
            String idNumber = rs.getString("con_id_number");
            String name = rs.getString("con_name");
            String phoneNumber = rs.getString("con_phone_number");
            int amount = rs.getInt("order_amount");
            rowData.addElement(idNumber);
            rowData.addElement(name);
            rowData.addElement(phoneNumber);
            rowData.addElement(amount);
            data.addElement(rowData);
        }
        close(rs, pst, conn);
    }
    //订单

    /**
     * 存储订单
     *
     * @param orderId     订单编号
     * @param goodsId     商品编号
     * @param goodsAmount 商品数量
     * @return 成功与否
     * @throws Exception 数据库连接异常
     */
    public static boolean saveGoodsInOrder(String orderId, String goodsId, int goodsAmount) throws Exception {
        Connection conn = getConnection();
        String sql = "insert into goods_in_order values(?,?,?) ";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, orderId);
        pst.setString(2, goodsId);
        pst.setInt(3, goodsAmount);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    //更新订单的汇款方式
    public static boolean updateOrderPayment(String orderId, String payment) throws Exception {
        Connection conn = getConnection();
        String sql = "update _order set payment=? where order_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, payment);
        pst.setString(2, orderId);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    //存储订单里的商品
    public static boolean saveOrder(String conId, String orderId) throws Exception {
        Connection conn = getConnection();
        String sql = "insert into _order values(?,?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, orderId);
        pst.setString(2, conId);
        pst.setString(3, Order.payByCash);
        pst.setBoolean(4, false);
        int i = pst.executeUpdate();
        close(pst, conn);
        return i == 1;
    }

    //根据订单编号获取订单里的商品
    public static void displayOrderData(Vector<Vector<Object>> data, String orderId) throws Exception {
        Connection conn = getConnection();
        String sql = "select * from goods_in_order where order_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, orderId);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            Vector<Object> rowData = new Vector<>();
            String idNumber = rs.getString("order_id_number");
            String goodsIdNumber = rs.getString("goods_id_number");
            int amount = rs.getInt("goods_amount");
            rowData.addElement(idNumber);
            rowData.addElement(goodsIdNumber);
            rowData.addElement(amount);
            data.addElement(rowData);
        }
        close(rs, pst, conn);
    }

    //根据顾客姓名获取订单里的商品，一次处理一个未发送的订单
    public static Order displayUnSendOrderData(String name) throws Exception {
        Connection conn = getConnection();
        Order order = null;
        String id = getIdNumberConsumer(name);
        String sql = "select * from _order where con_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            boolean b = rs.getBoolean("order_condition_sendable");
            if (!b) {
                String orderId = rs.getString("order_id_number");
                updateOrderCondition(orderId);
                order = new Order(orderId);
                String payment = rs.getString("payment");
                String sql2 = "select * from goods_in_order where order_id_number=?";
                PreparedStatement pst2 = conn.prepareStatement(sql2);
                pst2.setString(1, orderId);
                ResultSet rs2 = pst2.executeQuery();
                ArrayList<Goods> goodsArrayList = new ArrayList<>();
                while (rs2.next()) {
                    String goodsId = rs2.getString("goods_id_number");
                    int amount = rs2.getInt("goods_amount");
                    String goodsName = null;
                    double goodsPrice = 0;
                    String sql3 = "select * from goods where goods_id_number=?";
                    PreparedStatement pst3 = conn.prepareStatement(sql3);
                    pst3.setString(1, goodsId);
                    ResultSet rs3 = pst3.executeQuery();
                    while (rs3.next()) {
                        goodsName = rs3.getString("goods_name");
                        goodsPrice = rs3.getDouble("goods_price");
                    }
                    close(rs3, pst3);
                    Goods goods = new Goods(goodsId, goodsName, goodsPrice, amount);
                    goodsArrayList.add(goods);
                }
                close(rs2, pst2);
                order.setGoodsArrayList(goodsArrayList);
                order.setPayment(payment);
                break;
            }
        }

        close(rs, pst, conn);
        return order;
    }

    //更新订单的状态
    public static void updateOrderCondition(String orderId) throws Exception {
        Connection conn = getConnection();
        String sql = "update _order set order_condition_sendable=true where order_id_number=? ";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, orderId);
        pst.executeUpdate();
        close(pst, conn);
    }

    //根据顾客姓名判断是否有未发送的订单
    public static boolean isExistedUnSendOrder(String conName) throws Exception {
        Connection conn = getConnection();
        String id = getIdNumberConsumer(conName);
        String sql = "select * from _order where con_id_number=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, id);
        ResultSet rs = pst.executeQuery();
        while (rs.next()) {
            boolean b = rs.getBoolean("order_condition_sendable");
            if (!b) {
                close(rs, pst, conn);
                return true;
            }
        }
        close(rs, pst, conn);
        return false;
    }
}
